from datetime import datetime, timedelta
from illness_protection.models import *


class Day:

    @property
    def today(self):
        return datetime.today().strftime('%Y-%m-%d')

    @property
    def yesterday(self):
        return (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')

    @property
    def two_days_ago(self):
        return (datetime.today() - timedelta(days=2)).strftime('%Y-%m-%d')

    @property
    def three_days_ago(self):
        return (datetime.today() - timedelta(days=3)).strftime('%Y-%m-%d')

    @property
    def five_days_ago(self):
        return (datetime.today() - timedelta(days=5)).strftime('%Y-%m-%d')

    @property
    def six_days_ago(self):
        return (datetime.today() - timedelta(days=6)).strftime('%Y-%m-%d')

    @property
    def an_week_ago(self):
        return (datetime.today() - timedelta(days=7)).strftime('%Y-%m-%d')


day = Day()


class SqlList:

    @property
    def risk_area(self):
        return ','.join(item.region for item in ImportantArea.objects.filter(delete=0))

    @property
    def SELECT_NEW_RISK_GROUPS_WERE_ADDED_YESTERDAY(self):
        return '''
            SELECT
            today.submit_time,
            today.username,
            today.name,
            today.faculty_name,
            today.current_area,
            people_at_school_new.sj,
            get_health_situation(today.health_situation) as 'health_situation',
            get_health_situation(today.family_health_situation) as 'family_health_situation',
            is_specific_people(today.is_specific_people) as 'today_is_specific_people',
            is_specific_people(yesterday.is_specific_people) as 'yesterday_is_specific_people',
            is_specific_people(today.family_is_specific_people) as 'today_family_is_specific_people',
            is_specific_people(yesterday.family_is_specific_people) as 'yesterday_family_is_specific_people'
            FROM daily_submit_info_new today
            JOIN daily_submit_info_new yesterday ON yesterday.username = today.username and yesterday.submit_time = '{two_days_ago}' and (yesterday.is_specific_people = 1 OR yesterday.family_is_specific_people = 1)
            LEFT JOIN people_at_school_new on today.username = people_at_school_new.xh
            WHERE
            today.is_specific_people != 1 AND
            today.family_is_specific_people != 1 AND
            today.submit_time = '{yesterday}'
            '''.format(yesterday=day.yesterday, two_days_ago=day.two_days_ago)

    @property
    def PEOPLE_WHO_HAVE_BEEN_UNWELL_FOR_CONSECUTIVE_DAYS(self):
        return '''
            SELECT
            submit_time as '填报时间',
            username as '学工号',
            name as '姓名',
            faculty_name as '单位',
            ifnull(get_city_name(current_district),'广东省东莞市') as '居住地',
            tel as '手机',
            get_health_situation(health_situation) as '身体状况'
            FROM
            daily_submit_info_new
            LEFT JOIN people_at_school_new on daily_submit_info_new.username = people_at_school_new.xh
            WHERE submit_time = '{yesterday}' and username in (SELECT
            username
            FROM
            daily_submit_info_new
            WHERE submit_time >= '{five_days_ago}'
            GROUP BY 
            username
            HAVING
            min(health_situation) > 1)
            ORDER BY faculty_name;
            '''.format(yesterday=day.yesterday, five_days_ago=day.five_days_ago)

    @property
    def SQL_6(self):
        # 连续5日身体不适人数统计
        return '''
            SELECT
            submit_time,
            health_situation as 'health_situation',
            count(*) as 'num'
            FROM
            daily_submit_info_new
            WHERE submit_time = '{yesterday}' and username in (SELECT
            username
            FROM
            daily_submit_info_new
            WHERE submit_time >= '{an_week_ago}'
            GROUP BY 
            username
            HAVING
            min(health_situation) > 1)
            GROUP BY health_situation;'''.format(yesterday=day.yesterday, an_week_ago=day.an_week_ago)

    @property
    def SQL_7(self):
        # 风险评估
        return '''
            select
            t.risk_rank,
            count(t.username)
            FROM
            (
            SELECT
            username,
            `name`,
            clac_risk_ranks(current_city, have_gone_hubei, have_contact_hubei_people, have_contact_illness_people, health_situation) as 'risk_rank'
            FROM
            daily_submit_info_new
            WHERE
            submit_time = '{yesterday}'
            ORDER BY risk_rank desc
            ) t
            GROUP BY
            risk_rank;'''.format(yesterday=day.yesterday)

    @property
    def REPORTING_RATE_IN_RECENT_5_DAYS(self):
        # 近5日数据填报率
        return '''
            select t1.submit_time,t1.report_num,t2.count-t1.report_num as 'unreport_num',TRUNCATE(t1.report_num/t2.count*100, 2) as 'report_rate',TRUNCATE((1 - t1.report_num/t2.count)*100, 2) as 'unreport_rate'
            from 
            (
            SELECT
            submit_time,
            count(*) as report_num
            FROM
            daily_submit_info_new t1
            WHERE
            submit_time > '{six_days_ago}' AND 
            submit_time < '{today}'
            GROUP BY submit_time
            ORDER BY submit_time desc
            ) t1,
            (
            select count(*) as count
            from people_at_school_new
            ) t2
            '''.format(today=day.today, six_days_ago=day.six_days_ago)

    # 连续5日身体不适人数统计
    @property
    def PHYSICAL_CONDITION_OF_TEACHERS_AND_STUDENTS_IN_RECENT_5_DAYS(self):
        return '''
        SELECT
            submit_time,
            sum(health_situation=1) as 'good',
            sum(health_situation=2) as 'fever',
            sum(health_situation=3) as 'cough',
            sum(health_situation=4) as 'shortness_of_breath',
            sum(health_situation=5) as 'other',
            sum(health_situation=6) as 'dizziness',
            sum(health_situation=7) as 'diarrhea',
            sum(health_situation=8) as 'weak'
            FROM
            daily_submit_info_new
            WHERE submit_time > {five_days_ago}
            GROUP BY
            submit_time
            ORDER BY
            submit_time desc
            limit 5'''.format(five_days_ago=day.five_days_ago)

    @property
    def SQL_16(self):
        # 连续5日身体不适名单
        return '''
            SELECT
            submit_time as '填报时间',
            username as '学工号',
            name as '姓名',
            faculty_name as '单位',
            get_city_name(current_city) as '居住地',
            tel as '手机',
            get_health_situation(health_situation) as '身体状况'
            FROM
            daily_submit_info_new
            LEFT JOIN people_at_school_new on daily_submit_info_new.username = people_at_school_new.xh
            WHERE submit_time = '{yesterday}' and username in (SELECT
            username
            FROM
            daily_submit_info_new
            WHERE submit_time >= '{an_week_ago}'
            GROUP BY 
            username
            HAVING
            min(health_situation) > 1)
            ORDER BY faculty_name;'''.format(yesterday=day.yesterday, an_week_ago=day.an_week_ago)

    # 今日新增身体不适人群
    @property
    def get_sql_8(self):
        SQL_8 = '''
            SELECT
            today.username,
            today.name,
            today.faculty_name,
            get_city_name(today.current_city),
            today.tel,
            get_health_situation(today.health_situation) as 'health_situation'
            FROM daily_submit_info_new today
            JOIN daily_submit_info_new yestoday ON yestoday.username = today.username and yestoday.submit_time = '{yesterday}' and yestoday.health_situation = 1
            LEFT JOIN people_at_school_new on people_at_school_new.xh = today.username
            WHERE
            today.health_situation != 1 AND
            today.submit_time = '{today}' '''
        return SQL_8.format(today=day.today, yesterday=day.yesterday)

    @property
    def SQL_RECENT_FIVEDAY(self):
        return '''
                select a.xh,b.submit_time,b.place
                from people_at_school_new as a
                left join(
                    select username,submit_time,place
                    from daily_submit_info_new_addPlace
                    where 
                    submit_time in 
                    (
                        date_sub(curdate(),interval 5 day),
                        date_sub(curdate(),interval 4 day),
                        date_sub(curdate(),interval 3 day),
                        date_sub(curdate(),interval 2 day),
                        date_sub(curdate(),interval 1 day),
                        date_sub(curdate(),interval 0 day)
                    )
                    group by username,submit_time
                ) as b on a.xh=b.username
                '''

    @property
    def get_stu_tea_in_ill(self):
        SQL_STU_TEA_IN_ILL = '''
                select get_city_name(a.current_district),b.count,c.count
                from 
                (
                select
                current_district
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                is_in_risk_area(current_district) and type in ('student','foreign student')
                GROUP BY current_district
                union
                SELECT
                current_district
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                is_in_risk_area(current_district) and type in ('teacher','foreign teacher')
                GROUP BY current_district
                ) a
                left join 
                (
                select
                current_district,
                count(*) as 'count'
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                is_in_risk_area(current_district) and type in ('student','foreign student')
                GROUP BY current_district
                ) b on a.current_district=b.current_district
                left join 
                (
                SELECT
                current_district,
                count(*) as 'count'
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                is_in_risk_area(current_district) and type in ('teacher','foreign teacher')
                GROUP BY current_district
                ) c on a.current_district=c.current_district
                '''
        return SQL_STU_TEA_IN_ILL.format(today=day.today)

    @property
    def SQL_STU_TEA_IN_FOREIGN_ILL(self):
        return '''
                select 
                b.name,
                t1.student_num,
                t2.teacher_num
                FROM (
                SELECT
                current_country,
                count(*) as 'student_num'
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                current_country!=142 and type in ('foreign student', 'student') 
                GROUP BY current_country
                ) as t1
                right join (
                SELECT
                current_country,
                count(*) as 'teacher_num'
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                current_country!=142 and type in ('foreign teacher', 'teacher')
                GROUP BY current_country
                ) as t2 on t1.current_country = t2.current_country
                left join province_city_new b on t2.current_country=b.id 

                union 

                select 
                b.name,
                t1.student_num,
                t2.teacher_num
                FROM (
                SELECT
                current_country,
                count(*) as 'student_num'
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                current_country!=142 and type in ('foreign student', 'student')
                GROUP BY current_country
                ) as t1
                left join (
                SELECT
                current_country,
                count(*) as 'teacher_num'
                FROM daily_submit_info_new
                LEFT JOIN people_at_school_new ON people_at_school_new.xh = daily_submit_info_new.username
                WHERE submit_time = '{today}' and 
                current_country!=142 and type in ('foreign teacher', 'teacher')
                GROUP BY current_country
                ) as t2 on t1.current_country = t2.current_country
                left join province_city_new b on t1.current_country=b.id 

                '''.format(today=day.today)

    @property
    def SQL_TOP_CITIES(self):
        return '''
                SELECT
                get_city_name(t1.current_city) as 'city',
                count(t1.username) as 'num',
                round(count(t1.username)/(t2.count)*100, 2) as 'rate'
                FROM
                daily_submit_info_new t1
                join 
                (
                select count(xh) count
                from people_at_school_new
                ) t2
                WHERE
                submit_time = '{today}'
                GROUP BY
                current_city
                ORDER BY num desc
                limit 3;
                '''.format(today=day.today)

    @property
    def SQL_11(self):
        # 今日流入重点地区人员
        return '''
                SELECT
                '{today}' as '日期',
                yesterday.username as '学工号',
                yesterday.name as '姓名',
                yesterday.faculty_name as '单位',
                get_14_health_condition(yesterday.username) as '近14天身体状况',
                yesterday.tel as '手机号',
                get_city_name_helper(yesterday.current_country) as 'yesterday_country',
                get_city_name_helper(yesterday.current_province) as 'yesterday_province',
                get_city_name_helper(yesterday.current_city) as 'yesterday_city',
                get_city_name_helper(yesterday.current_district) as 'yesterday_district',

                get_city_name_helper(today.current_country) as 'today_country',
                get_city_name_helper(today.current_province) as 'today_province',
                get_city_name_helper(today.current_city) as 'today_city',
                get_city_name_helper(today.current_district) as 'today_district'
                FROM
                daily_submit_info_new yesterday
                JOIN daily_submit_info_new today ON today.submit_time = '{today}' AND today.username = yesterday.username
                WHERE yesterday.submit_time = '{yesterday}'
                AND yesterday.current_country = 142 
                AND (yesterday.current_district IS NULL OR yesterday.current_district NOT IN ({risk_area}))
                AND today.current_district IN ({risk_area})
                '''.format(today=day.today, yesterday=day.yesterday, risk_area=self.risk_area)


    @property
    def SQL_11_TEA(self):
        # 今日流入重点地区人员
        return '''
                SELECT
                '{today}' as '日期',
                yesterday.username as '学工号',
                yesterday.name as '姓名',
                yesterday.faculty_name as '单位',
                get_14_health_condition(yesterday.username) as '近14天身体状况',
                yesterday.tel as '手机号',
                get_city_name_helper(yesterday.current_country) as 'yesterday_country',
                get_city_name_helper(yesterday.current_province) as 'yesterday_province',
                get_city_name_helper(yesterday.current_city) as 'yesterday_city',
                get_city_name_helper(yesterday.current_district) as 'yesterday_district',

                get_city_name_helper(today.current_country) as 'today_country',
                get_city_name_helper(today.current_province) as 'today_province',
                get_city_name_helper(today.current_city) as 'today_city',
                get_city_name_helper(today.current_district) as 'today_district'
                FROM
                people_at_school_new t1
                left join daily_submit_info_new yesterday on t1.xh=yesterday.username
                JOIN daily_submit_info_new today ON today.submit_time = '{today}' AND today.username = yesterday.username
                WHERE yesterday.submit_time = '{yesterday}'
                AND yesterday.current_country = 142 
                AND (yesterday.current_district IS NULL OR yesterday.current_district NOT IN ({risk_area}))
                AND today.current_district IN ({risk_area})
                AND t1.type in ('foreign teacher', 'teacher') 
                '''.format(today=day.today, yesterday=day.yesterday, risk_area=self.risk_area)


    @property
    def SQL_11_STU(self):
        # 今日流入重点地区人员
        return '''
                SELECT
                '{today}' as '日期',
                yesterday.username as '学工号',
                yesterday.name as '姓名',
                yesterday.faculty_name as '单位',
                get_14_health_condition(yesterday.username) as '近14天身体状况',
                yesterday.tel as '手机号',
                get_city_name_helper(yesterday.current_country) as 'yesterday_country',
                get_city_name_helper(yesterday.current_province) as 'yesterday_province',
                get_city_name_helper(yesterday.current_city) as 'yesterday_city',
                get_city_name_helper(yesterday.current_district) as 'yesterday_district',

                get_city_name_helper(today.current_country) as 'today_country',
                get_city_name_helper(today.current_province) as 'today_province',
                get_city_name_helper(today.current_city) as 'today_city',
                get_city_name_helper(today.current_district) as 'today_district'
                FROM
                people_at_school_new t1
                left join daily_submit_info_new yesterday on t1.xh=yesterday.username
                JOIN daily_submit_info_new today ON today.submit_time = '{today}' AND today.username = yesterday.username
                WHERE yesterday.submit_time = '{yesterday}'
                AND yesterday.current_country = 142 
                AND (yesterday.current_district IS NULL OR yesterday.current_district NOT IN ({risk_area}))
                AND today.current_district IN ({risk_area})
                AND t1.type in ('foreign student', 'student') 
                '''.format(today=day.today, yesterday=day.yesterday, risk_area=self.risk_area)

    @property
    def SQL_12(self):
        return '''
                SELECT count(DISTINCT username)
                FROM daily_submit_info_new
                WHERE
                have_contact_hubei_people = 1;'''

    @property
    def SQL_13(self):
        return '''
                SELECT count(DISTINCT username)
                FROM daily_submit_info_new
                WHERE
                have_contact_illness_people = 1;'''

    @property
    def SQL_14(self):
        return '''
                SELECT count(DISTINCT username)
                FROM daily_submit_info_new
                WHERE
                have_contact_illness_people = 1 AND have_contact_hubei_people = 1;'''

    @property
    def SQL_15(self):
        # 今日人员流动统计
        return '''
                SELECT
                get_city_name(today.current_city) as 'city',
                count(*) as 'num'
                #today.submit_time,
                #yesterday.submit_time as 'yesterday',
                #today.username,
                #today.name,
                #today.current_city as 'today_city',
                #yesterday.current_city as 'yesterday_city'

                FROM
                daily_submit_info_new today
                LEFT JOIN daily_submit_info_new yesterday ON today.username = yesterday.username and yesterday.submit_time = '{yesterday}'
                WHERE today.submit_time = '{today}' and today.current_city != yesterday.current_city
                GROUP BY
                today.current_city
                ORDER BY num desc
                limit 3
                '''.format(today=day.today, yesterday=day.yesterday)

    @property
    def SQL_15_STU(self):
        return '''
                SELECT
                get_city_name(today.current_city) as 'city',
                count(*) as 'num'
                FROM
                people_at_school_new t1
                left join daily_submit_info_new today on t1.xh=today.username
                LEFT JOIN daily_submit_info_new yesterday ON today.username = yesterday.username and yesterday.submit_time = '{yesterday}'
                WHERE today.submit_time = '{today}' and today.current_city != yesterday.current_city and t1.type in ('student', 'foreign student')
                GROUP BY
                today.current_city
                ORDER BY num desc
                limit 3
                '''.format(today=day.today, yesterday=day.yesterday)


    
    @property
    def SQL_15_TEA(self):
        return '''
                SELECT
                get_city_name(today.current_city) as 'city',
                count(*) as 'num'
                FROM
                people_at_school_new t1
                left join daily_submit_info_new today on t1.xh=today.username
                LEFT JOIN daily_submit_info_new yesterday ON today.username = yesterday.username and yesterday.submit_time = '{yesterday}'
                WHERE today.submit_time = '{today}' and today.current_city != yesterday.current_city and t1.type in ('teacher', 'foreign teacher')
                GROUP BY
                today.current_city
                ORDER BY num desc
                limit 3
                '''.format(today=day.today, yesterday=day.yesterday)

    @property
    def get_ill_count(self):
        SQL_ILL_COUNT = '''
                select
                max(case when type='teacher' then count else null end) as stuCount,
                max(case when type='student' then count else null end) as teaCount
                from(
                select a.type,count(*) count
                from people_at_school_new a
                left join daily_submit_info_new b on b.username=a.xh
                where b.submit_time = '{today}' and is_in_risk_area(b.current_district)
                group by type
                ) c
                '''
        return SQL_ILL_COUNT.format(today=day.today)

    @property
    def SQL_PLAN_BACK_DG(self):
        # 获取计划回东莞的出行方式、出行时间人数
        return '''
                select plan_back_dg_time as plan_back_dg_time,
                case back_dg_transportation 
                when 0 then "火车" when 1 then "飞机" 
                when 2 then "轮船" when 3 then "长途公共巴士" 
                when 4 then "自驾" when 5 then "其他" end as back_dg_transportation,count(*) count
                from daily_submit_info_new
                where submit_time = '{today}' and plan_back_dg_time is not null
                group by plan_back_dg_time,back_dg_transportation
                order by plan_back_dg_time asc
                '''.format(today=day.today)

    @property
    def SQL_PLAN_BACK_DG_DAY(self):
        return '''
                select plan_back_dg_time, count(*) count
                from daily_submit_info_new
                where submit_time = '{today}' and plan_back_dg_time is not null
                group by plan_back_dg_time, back_dg_transportation
                order by count desc
                limit 5'''.format(today=day.today)

    @property
    def SQL_PLAN_BACK_DG_TRANSPORT(self):
        return '''
                select get_transportation(back_dg_transportation) as transportation,count(*) count
                from daily_submit_info_new
                where submit_time = '{today}' and plan_back_dg_time is not null
                group by back_dg_transportation
                order by count desc
                '''.format(today=day.today)

    # 有计划回东莞的人数
    @property
    def get_plan_back_dg_cal(self):
        SQL_PLAN_BACK_DG_CAL = '''
                select 
                count(*) count,
                count(case when substring(plan_back_dg_time,7,1)<(month(curdate())+1) then 1 end) as beforet,
                count(case when substring(plan_back_dg_time,7,1)>=(month(curdate())+1) then 1 end) as aftert
                from daily_submit_info_new
                where submit_time = '{today}' and plan_back_dg_time is not null
                '''
        return SQL_PLAN_BACK_DG_CAL.format(today=day.today)

    @property
    def get_sql_at_dg_count(self):
        SQL_AT_DG_COUNT = '''
                select a.count,b.count
                from 
                (
                select count(*) count
                from daily_submit_info_new
                where is_in_dg=0 and submit_time = '{today}'
                ) as a 
                join 
                (
                select count(*) count
                from daily_submit_info_new
                where is_in_dg!=0 and submit_time = '{today}'
                ) as b 
                '''
        return SQL_AT_DG_COUNT.format(today=day.today)

    @property
    def SQL_17(self):
        # 今日流出疫情重点关注地区人员
        return '''
            SELECT
            today.submit_time,
            today.username,
            today.NAME,
            today.faculty_name,
            get_14_health_condition ( today.username ) AS '近14天身体状况',
            today.tel AS '手机号',
            get_city_name_helper(yesterday.current_country) as 'yesterday_country',
            get_city_name_helper(yesterday.current_province) as 'yesterday_province',
            get_city_name_helper(yesterday.current_city) as 'yesterday_city',
            get_city_name_helper(yesterday.current_district) as 'yesterday_district',

            get_city_name_helper(today.current_country) as 'today_country',
            get_city_name_helper(today.current_province) as 'today_province',
            get_city_name_helper(today.current_city) as 'today_city',
            get_city_name_helper(today.current_district) as 'today_district' 
            FROM daily_submit_info_new today 
            JOIN daily_submit_info_new yesterday ON  yesterday.username = today.username 
            WHERE today.submit_time = '{today}' 
            AND yesterday.submit_time = '{yesterday}'
            AND today.current_country = 142 
            AND (today.current_district IS NULL OR today.current_district NOT IN ({risk_area}) ) 
            AND yesterday.current_district IN ({risk_area})
            '''.format(today=day.today, yesterday=day.yesterday, risk_area=self.risk_area)


    @property
    def SQL_17_TEA(self):
        # 今日流出疫情重点关注地区人员
        return '''
            SELECT
            today.submit_time,
            today.username,
            today.NAME,
            today.faculty_name,
            get_14_health_condition ( today.username ) AS '近14天身体状况',
            today.tel AS '手机号',
            get_city_name_helper(yesterday.current_country) as 'yesterday_country',
            get_city_name_helper(yesterday.current_province) as 'yesterday_province',
            get_city_name_helper(yesterday.current_city) as 'yesterday_city',
            get_city_name_helper(yesterday.current_district) as 'yesterday_district',

            get_city_name_helper(today.current_country) as 'today_country',
            get_city_name_helper(today.current_province) as 'today_province',
            get_city_name_helper(today.current_city) as 'today_city',
            get_city_name_helper(today.current_district) as 'today_district' 
            from people_at_school_new t1
            left join daily_submit_info_new today on t1.xh=today.username
            JOIN daily_submit_info_new yesterday ON  yesterday.username = today.username 
            WHERE today.submit_time = '{today}' 
            AND yesterday.submit_time = '{yesterday}'
            AND today.current_country = 142 
            AND (today.current_district IS NULL OR today.current_district NOT IN ({risk_area}) ) 
            AND yesterday.current_district IN ({risk_area})
            AND t1.type in ('teacher','foreign teacher')
            '''.format(today=day.today, yesterday=day.yesterday, risk_area=self.risk_area)




    @property
    def SQL_17_STU(self):
        # 今日流出疫情重点关注地区人员
        return '''
            SELECT
            today.submit_time,
            today.username,
            today.NAME,
            today.faculty_name,
            get_14_health_condition ( today.username ) AS '近14天身体状况',
            today.tel AS '手机号',
            get_city_name_helper(yesterday.current_country) as 'yesterday_country',
            get_city_name_helper(yesterday.current_province) as 'yesterday_province',
            get_city_name_helper(yesterday.current_city) as 'yesterday_city',
            get_city_name_helper(yesterday.current_district) as 'yesterday_district',

            get_city_name_helper(today.current_country) as 'today_country',
            get_city_name_helper(today.current_province) as 'today_province',
            get_city_name_helper(today.current_city) as 'today_city',
            get_city_name_helper(today.current_district) as 'today_district' 
            from people_at_school_new t1
            left join daily_submit_info_new today on t1.xh=today.username
            JOIN daily_submit_info_new yesterday ON  yesterday.username = today.username 
            WHERE today.submit_time = '{today}' 
            AND yesterday.submit_time = '{yesterday}'
            AND today.current_country = 142 
            AND (today.current_district IS NULL OR today.current_district NOT IN ({risk_area}) ) 
            AND yesterday.current_district IN ({risk_area})
            AND t1.type in ('student','foreign student')
            '''.format(today=day.today, yesterday=day.yesterday, risk_area=self.risk_area)



    @property
    def SQL_GET_RISK_TWO(self):
        return '''
                SELECT
                username,
                `name`,
                tel,
                faculty_name,
                get_city_name(current_city),
                case have_contact_hubei_people when 0 then "否" when 1 then "是" end as have_contact_hubei_people,
                case have_contact_illness_people when 0 then "否" when 1 then "是" end as have_contact_illness_people,
                get_real_health_situation(health_situation) as health_situation,
                is_check(学工号)
                FROM
                daily_submit_info_new
                WHERE
                submit_time = '{yesterday}' and clac_risk_ranks(current_city, have_gone_hubei, have_contact_hubei_people, have_contact_illness_people, health_situation)=2
                '''.format(yesterday=day.yesterday)

    @property
    def get_future_back_dongguan_people_num_statistics(self):
        sql = '''
                select plan_back_dg_time,count(*) count
                from daily_submit_info_new
                where submit_time = '{yesterday}' and plan_back_dg_time > '{yesterday}'
                group by plan_back_dg_time
                order by plan_back_dg_time asc
                limit 14
                '''
        return sql.format(yesterday=day.yesterday)

    # 最多人计划回莞的三天
    @property
    def get_plan_back_dg_count_desc(self):
        SQL_PLAN_BACK_DG_COUNT_DESC = '''
                select plan_back_dg_time,count(*) count
                from daily_submit_info_new
                where submit_time = '{yesterday}' and plan_back_dg_time is not null
                group by plan_back_dg_time
                order by count desc
                limit 3
                '''
        return SQL_PLAN_BACK_DG_COUNT_DESC.format(yesterday=day.yesterday)

    # 十四天前实际行程
    @property
    def get_past_back_dongguan_people_num_statistics(self):
        sql = '''
                select *
                from
                (
                select finally_back_time,count(*) count
                from daily_submit_info_new
                where submit_time = '{today}' and finally_back_time < '{today}'
                group by finally_back_time
                order by finally_back_time desc
                limit 14
                ) as a
                order by finally_back_time asc'''

        return sql.format(today=day.today)

    # 二级组织未填报率
    @property
    def get_second_nocount(self):
        SQL_SECOND_NOCOUNT = '''
                select a.yxmc as 单位名称,a.count-b.nocount as 填报人数,b.nocount as 未填报人数,case when round((b.nocount/a.count)*100,2) is null then 0 else round((b.nocount/a.count)*100,2) end as 未填报率,a.count as 单位人数
                from 
                (
                select b.label as yxmc,yxdm,count(*) as count
                from people_at_school_new as a 
                left join dwbmdm as b on a.yxdm=b.value
                where yxdm is not null
                group by yxdm
                ) as a
                join 
                (
                select case when a.yxdm is null then 0 else a.yxdm end as yxdm,count(*) nocount
                from people_at_school_new as a 
                left join 
                (
                select username
                from daily_submit_info_new 
                where submit_time = '{yesterday}'
                ) as b on a.xh=b.username
                where b.username is null
                group by a.yxdm
                order by count(*) desc 
                ) as b on a.yxdm=b.yxdm
                group by b.nocount/a.count desc
                '''
        return SQL_SECOND_NOCOUNT.format(yesterday=day.yesterday)

    @property
    def SQL_GET_PEOPLE_INFO(self):
        return '''
                select count(*)
                from people_at_school_new
                where type in ('teacher')

                union all

                select count(*)
                from people_at_school_new
                where type in ('foreign teacher')

                union all

                select count(*)
                from people_at_school_new
                where type in ('student')

                union all

                select count(*)
                from people_at_school_new
                where type in ('foreign student')

                union all

                select count(*)
                from people_at_school_new
                '''

    # 新增离莞离粤名单
    @property
    def get_out_dongguan(self):
        sql_out_dongguan = '''
                SELECT
                today.submit_time,
                today.username,
                today.name,
                today.faculty_name,
                get_14_health_condition(today.username) as '近14天身体状况',
                today.tel as '手机号',
                ifnull(get_city_name(get_country_or_city_name(get_current_country('{yesterday}', today.username), get_current_city('{yesterday}', today.username))),  get_country_or_city_name(get_current_country('{yesterday}', today.username), get_current_city('{yesterday}', today.username))) as '前一日所在地',
                ifnull(get_city_name(today.current_district),get_country_or_city_name(today.current_country, today.current_city)) as '现今所在地'
                FROM
                daily_submit_info_new today
                WHERE today.submit_time = '{today}' AND exists (
                    SELECT
                    current_city
                    FROM
                    daily_submit_info_new yesterday
                    WHERE yesterday.submit_time = '{yesterday}' and yesterday.current_city=441900 and yesterday.username = today.username
                ) and today.current_country = 142 and not today.current_city=441900
                '''
        return sql_out_dongguan.format(today=day.today, yesterday=day.yesterday)

    # 离粤名单
    @property
    def get_out_guangdong(self):
        sql_out_guangdong = '''
                SELECT
                today.submit_time,
                today.username,
                today.name,
                today.faculty_name,
                get_14_health_condition(today.username) as '近14天身体状况',
                today.tel as '手机号',
                ifnull(get_city_name(get_country_or_city_name(get_current_country('{yesterday}', today.username), get_current_city('{yesterday}', today.username))),  get_country_or_city_name(get_current_country('{yesterday}', today.username), get_current_city('{yesterday}', today.username))) as '前一日所在地',
                ifnull(get_city_name(today.current_district),get_country_or_city_name(today.current_country, today.current_city)) as '现今所在地'
                FROM
                daily_submit_info_new today
                WHERE today.submit_time = '{today}' AND exists (
                    SELECT
                    current_city
                    FROM
                    daily_submit_info_new yesterday
                    WHERE yesterday.submit_time = '{yesterday}' and yesterday.current_province=440000 and yesterday.username = today.username
                ) and today.current_country = 142 and not today.current_province=440000
                '''
        return sql_out_guangdong.format(today=day.today, yesterday=day.yesterday)

    @property
    def sql_importment_area(self):
        return '''
                select label,label2
                from important_area
                where `delete`=0
                '''

    @property
    def sql_importment_area_count(self):
        return '''
                select count(*)
                from important_area
                where `delete`=0
                '''

    @property
    def abnormal_body_temperature_student(self):
        return '''
                SELECT
                daily_submit_info_new.submit_time,
                daily_submit_info_new.username,
                daily_submit_info_new.`name`,
                daily_submit_info_new.faculty_name,
                daily_submit_info_new.class_name,
                get_health_situation(daily_submit_info_new.health_situation),
                daily_submit_info_new.body_temperature,
                people_at_school_new.sj
                FROM
                daily_submit_info_new
                LEFT JOIN people_at_school_new ON daily_submit_info_new.username = people_at_school_new.xh
                WHERE
                daily_submit_info_new.submit_time = '{today}' AND
                people_at_school_new.type IN ('student', 'foreign student') AND
                daily_submit_info_new.body_temperature >= 37.3
                ORDER BY body_temperature desc'''.format(today=day.today)

    @property
    def continuous_abnormal_body_temperature_student(self):
        return '''
                SELECT
                daily_submit_info_new.username,
                daily_submit_info_new.`name`,
                daily_submit_info_new.faculty_name,
                daily_submit_info_new.class_name,
                get_health_situation(daily_submit_info_new.health_situation),
                daily_submit_info_new.body_temperature,
                count(*) as continuous_days,
                daily_submit_info_new.tel
                FROM
                daily_submit_info_new
                WHERE
                daily_submit_info_new.submit_time > '{three_days_ago}' AND 
                daily_submit_info_new.body_temperature >= 37.3
                GROUP BY username
                having count(*) >= 3
                ORDER BY continuous_days desc
                '''.format(three_days_ago=day.three_days_ago)
    

    # 健康码统计
    @property
    def healthy_status_total_tea(self):
        return '''
            select sum(t3.health_code_status=1) green, sum(t3.health_code_status=2) yellow, sum(t3.health_code_status=3) red
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            where t2.submit_time='{today}' and t1.type in ('teacher','foreign teacher')
        '''.format(today=day.today)



    @property
    def healthy_status_total_stu(self):
        return '''
            select sum(t3.health_code_status=1) green, sum(t3.health_code_status=2) yellow, sum(t3.health_code_status=3) red
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            where t2.submit_time='{today}' and t1.type in ('student','foreign student')
        '''.format(today=day.today)


    # 红黄码详细人员名单
    @property
    def healthy_status_detail_tea(self):
        return '''
            select t2.username,t1.xm,t4.label, t5.bjmc,get_health_situation(t2.health_situation),
            case t3.health_code_status 
            when 2 then '黄码'
            when 3 then '红码'
            end health_code_status
            ,t1.sj
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            left join dwbmdm t4 on t1.yxdm=t4.`value`
            left join gxxx04 t5 on t1.bjdm=t5.bh
            where t2.submit_time='{today}' and t3.health_code_status in (2,3) and t1.type in ('teacher','foreign teacher')
            order by t3.health_code_status,username
        '''.format(today=day.today)

    @property
    def healthy_status_detail_stu(self):
        return '''
            select t2.username,t1.xm,t4.label, t5.bjmc,get_health_situation(t2.health_situation),
            case t3.health_code_status 
            when 2 then '黄码'
            when 3 then '红码'
            end health_code_status
            ,t1.sj
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            left join dwbmdm t4 on t1.yxdm=t4.`value`
            left join gxxx04 t5 on t1.bjdm=t5.bh
            where t2.submit_time='{today}' and t3.health_code_status in (2,3) and t1.type in ('student','foreign student')
            order by t3.health_code_status,username
        '''.format(today=day.today)

    # 管控区域人员统计
    @property
    def healthy_controller_area_total_tea(self):
        return '''
            select sum(t3.in_controllerd_area=1) in_controllerd_area1,sum(t3.in_controllerd_area=2) in_controllerd_area2
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            where t2.submit_time='{today}' and t3.in_controllerd_area in (1,2) and t1.type in ('teacher','foreign teacher')
        '''.format(today=day.today)

    @property
    def healthy_controller_area_total_stu(self):
        return '''
            select sum(t3.in_controllerd_area=1) in_controllerd_area1,sum(t3.in_controllerd_area=2) in_controllerd_area2
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            where t2.submit_time='{today}' and t3.in_controllerd_area in (1,2) and t1.type in ('student','foreign student')
            group by t3.in_controllerd_area
        '''.format(today=day.today)


    # 师生疫苗接种情况
    @property
    def healthy_vaccination_total_tea(self):
        return '''
            select sum(t3.completed_vaccination=1) ym_two, sum(t3.completed_vaccination=2) ym_one,sum(t3.completed_vaccination=3) ym_no_normal,sum(t3.completed_vaccination=4) ym_no_error
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            where t2.submit_time='{today}' and t1.type in ('teacher','foreign teacher')
        '''.format(today=day.today)


    @property
    def healthy_vaccination_total_stu(self):
        return '''
            select sum(t3.completed_vaccination=1) ym_two, sum(t3.completed_vaccination=2) ym_one,sum(t3.completed_vaccination=3) ym_no_normal,sum(t3.completed_vaccination=4) ym_no_error
            from people_at_school_new t1
            left join daily_submit_info_new t2 on t1.xh=t2.username
            left join daily_submit_info_ext t3 on t2.id=t3.submit_id
            where t2.submit_time='{today}' and t1.type in ('student','foreign student')
        '''.format(today=day.today)


sqlList = SqlList()